﻿using System;
using System.Drawing;
using Finance.Data.Entities;
using Finance.Properties;
using TodoSoft;
using TodoSoft.Reporting;

namespace Finance.Reports
{
	public class InflationBySellersReport : Report
	{
		public InflationBySellersReport()
		{
			DataQuery = @"select 
  maindp.mainpid as productid, 
  product.name as productname,
  maindp.maincid as contractorid,
  contractor.name as contractorname,
  maxdoc.maxprice as maxprice,
  maxdoc.maxdocdate as maxdocdate,
  mindoc.minprice as minprice,
  mindoc.mindocdate as mindocdate,
  case mindoc.minprice 
    when 0 then 0
    else ((maxdoc.maxprice - mindoc.minprice) / mindoc.minprice) end as inflation
from (
select 
    d1.contractorid as maincid, 
    dp1.productid as mainpid, 
    max(d1.docdatetime) as maxdate,
    min(d1.docdatetime) as mindate 
  from 
    document as d1 
  inner join 
    documentproduct as dp1 on d1.documentid = dp1.documentid 
  where
    d1.docdatetime >= @Period_start and d1.docdatetime <= @Period_end
  group by 
    d1.contractorid, 
    dp1.productid
)  as maindp
inner join (
  select 
    documentproduct.price as maxprice, 
    document.docdatetime as maxdocdate, 
    document.contractorid as maxcid, 
    documentproduct.productid as maxpid
  from 
    document 
  inner join
    documentproduct on document.documentid = documentproduct.documentid
  where
    document.typecode = " + (int)DocumentType.Rashod + @"
) as maxdoc
on 
  maindp.maincid = maxdoc.maxcid and
  maindp.maxdate = maxdoc.maxdocdate and
  maindp.mainpid = maxdoc.maxpid
inner join (
  select 
    documentproduct.price as minprice, 
    document.docdatetime as mindocdate, 
    document.contractorid as mincid, 
    documentproduct.productid as minpid
  from 
    document 
  inner join
    documentproduct on document.documentid = documentproduct.documentid
  where
    document.typecode = " + (int)DocumentType.Rashod + @"
) as mindoc
on 
  maindp.maincid = mindoc.mincid and
  maindp.mindate = mindoc.mindocdate and
  maindp.mainpid = mindoc.minpid
inner join 
  product on product.productid = maindp.mainpid
inner join 
  contractor on contractor.contractorid = maindp.maincid
order by 
  productname";

			var contractorGroup = new ReportGrouping("contractorid") {Font = new Font("Arial", 10, FontStyle.Bold)};
			contractorGroup.Fields.Add(ReportDataField.First("contractorname", 170, Resources.ContractorsReport_ContractorNameColumn));
			//contractorGroup.Fields.Add("", 50, Resources.ContractorsReport_ContractorAverageInflation);
			Groupings.Add(contractorGroup);

			var productGroup = new ReportGrouping("productid")
				{
					Font = new Font("Arial", 8, FontStyle.Regular),
					HeaderRowVisible = false
				};
			var productNameColumn = ReportDataField.First("productname", 80, Resources.ContractorsReport_ProductNameColumn);
			productNameColumn.Padding = new Margins(5, 0, 0, 0);
			productGroup.Fields.Add(productNameColumn);
			var minPriceField = ReportDataField.First("minprice", 50, Resources.ContractorsReport_ProductPriceStartColumn);
			minPriceField.StringFormat = new StringFormat{Alignment = StringAlignment.Far};
			minPriceField.FormatArgument = "C";
			productGroup.Fields.Add(minPriceField);
			var maxPriceField = ReportDataField.First("maxprice", 50, Resources.ContractorsReport_ProductPriceEndColumn);
			maxPriceField.StringFormat = new StringFormat { Alignment = StringAlignment.Far };
			maxPriceField.FormatArgument = "C";
			productGroup.Fields.Add(maxPriceField);
			var inflationField = ReportDataField.First("inflation", 50, Resources.ContractorsReport_ProductInflationColumn);
			inflationField.StringFormat = new StringFormat { Alignment = StringAlignment.Far };
			inflationField.FormatArgument = "P";
			productGroup.Fields.Add(inflationField);
			Groupings.Add(productGroup);

			Parameters.Add(new PeriodReportParameter("Period")
			{
				MinValueQuery = "select min(docdatetime) from document",
				MaxValueQuery = "select max(docdatetime) from document"
			});
		}
	}
}
